<?php
class ViSeminar_Controller extends AdminController {
    
    public function __construct() {
        parent::__construct('viSeminar');
        $this->setDefaultListOrder('begin_date', 'DESC');
    }

    function overview() {
        // Upcoming viSeminars
        $viSeminarList = $this->dao->queryAll(
                'SELECT * FROM vi_seminar WHERE end_date >= :end_date ORDER BY begin_date ASC, begin_time ASC LIMIT 8',
                array('end_date' => sql::$TODAY));
        $this->viewData['upcomingViSeminarList'] = $viSeminarList;
        foreach ($this->viewData['upcomingViSeminarList'] as &$viSeminar) {
            $viSeminar['registrationCount'] = $this->dao->registrationCountOfViSeminar($viSeminar['id']);
        }

        // Recent viSeminars (useful for accounting)
        $viSeminarList = $this->dao->queryAll(
                'SELECT * FROM vi_seminar WHERE end_date < :end_date ORDER BY begin_date DESC, begin_time DESC LIMIT 3',
                array('end_date' => sql::$TODAY));
        $this->viewData['recentViSeminarList'] = $viSeminarList;
        foreach ($this->viewData['recentViSeminarList'] as &$viSeminar) {
            $viSeminar['registrationCount'] = $this->dao->registrationCountOfViSeminar($viSeminar['id']);
        }

        $this->readViSeminarOptions();

        $this->renderMySubview('viSeminarOverview');
    }
    
    function all() {
        $this->initPagination('vi_seminar');

        $query = 'SELECT sem.* FROM vi_seminar sem ';
        $queryParams = array();

        // Search criteria
        $whereClause = '';
        $whereParams = array();
        $searchCriteria = $this->getSearchCriteria();
        foreach ($searchCriteria as $key => $value) {
            if ($value === NULL || trim($value) === '') {
                continue;
            }
            $whereClause .= 'sem.' . $key . ' = :' . $key . ' AND ';
            $queryParams[$key] = $value;
        }
        if ($whereClause) {
            $query .= 'WHERE ' . substr($whereClause, 0, -4);
        }
        
        $this->viewData['viSeminarList'] = $this->dao->queryAll(
                $this->orderLimitAndOffset($query), $queryParams);

        $this->readViSeminarOptions();

        $this->renderSubview('admin/viSeminar/viSeminarList');
    }

    private function readViSeminarOptions() {
        // Quick search values
        $quickSearchValues = array();
        $quickSearchList = $this->dao->queryAll(
                'SELECT id, name, begin_date, end_date, instructor FROM vi_seminar ' .
                'ORDER BY begin_date DESC');
        foreach ($quickSearchList as $seminar) {
            $quickSearchValues[$seminar['id']] = $seminar['name'] . ' - ' . $seminar['instructor'] .
                    ' (' . format::date($seminar['begin_date']) . ' - ' . format::date($seminar['end_date']) . ')';
        }
        $this->viewData['viSeminarOptions'] = $quickSearchValues;
    }
    
    function display() {
        $id = $this->input->get('id');

        if (! valid::isId($id)) {
            $this->redirectToList('error.invalidId');
        }
        
        $viSeminar = $this->dao->fetchViSeminarWithPrices($id);
        if (! $viSeminar) {
            $this->redirectToList('error.recordNotFound');
        }

        $viSeminar['bank_account'] = $this->dao->getById('bank_account', $viSeminar['bank_account_id']);

        // Select the count of registrations
        $viSeminar['registrationCount'] = $this->dao->registrationCountOfViSeminar($viSeminar['id']);

        $this->viewData['currencyCodeList'] = $this->dao->selectViSeminarCurrecyCodeList($id);
        $this->viewData['viAccomodationTypePrices'] = $this->dao->findViAccomodationTypesWithPrices($id);
        $this->viewData['viSeminar'] = $viSeminar;
        $this->viewData['viSeminarTagList'] = $this->dao->queryAll(
                'SELECT tag.* FROM vi_seminar_tag_asoc asoc JOIN vi_seminar_tag tag ON asoc.vi_seminar_tag_id = id ' .
                'WHERE vi_seminar_id = :id', array('id' => $id));
        $this->renderSubview('admin/viSeminar/viSeminarDisplay');
    }
    
    function editPrices() {
        $id = $this->input->get('id');
        $pricesForm = array();

        if ($id) {
            $viSeminar = $this->dao->getById('vi_seminar', $id);
            if (! $viSeminar) {
                $this->redirectToList('error.recordNotFound');
            }
        }
        else {
            $this->redirectToList('error.invalidId');
        }

        $currencyCode = 'CZK';

        $this->editPricesData($id);

        // Load prices of the seminar
        $prices = $this->dao->getViSeminarPricesByCurrency($id, $currencyCode);

        $pricesForm[$currencyCode] = array();
        $pricesForm[$currencyCode]['course_fee'] = $prices['course_fee'];
        $pricesForm[$currencyCode]['bank_payment_discount'] = $prices['bank_payment_discount'];
        $pricesForm[$currencyCode]['social_discount'] = $prices['social_discount'];
        $pricesForm[$currencyCode]['breakfast_price'] = $prices['breakfast_price'];
        $pricesForm[$currencyCode]['lunch_price'] = $prices['lunch_price'];
        $pricesForm[$currencyCode]['dinner_price'] = $prices['dinner_price'];

        // Load seminar accomodation prices
        $accTypeCodes = array();
        foreach ($this->viewData['viAccomodationTypeList'] as $accType) {
            $accTypeCodes[] = $accType['code'];
        }
        $accPrices = $this->dao->queryAll(
                'SELECT * FROM vi_seminar_accomodation_type_price ' .
                'WHERE vi_seminar_id = :vi_seminar_id AND vi_accomodation_type_code IN (' . sql::inClauseValues($accTypeCodes) . ')',
                array('vi_seminar_id' => $id));
        foreach ($accPrices as $price) {
            $pricesForm['viAccomodationTypePrice' . $currencyCode][$price['vi_accomodation_type_code']] = $price['price'];
        }

        // First and last meal
        $pricesForm['first_meal'] = $viSeminar['first_meal'];
        $pricesForm['last_meal'] = $viSeminar['last_meal'];

        $this->viewData['viSeminar'] = $viSeminar;
        $this->viewData['pricesForm'] = $pricesForm;
        $this->renderSubview('admin/viSeminar/viSeminarEditPrices');
    }

    function editPricesData($id) {
        // Meal options
        $this->viewData['mealOptions'] = array(
            'breakfast' => text::get('viSeminar.breakfast'),
            'lunch' => text::get('viSeminar.lunch'),
            'dinner' => text::get('viSeminar.dinner')
        );

        // Load accomodation types available for this seminar
        $this->viewData['viAccomodationTypeList'] = $this->dao->queryAll(
                'SELECT acctype.* FROM vi_seminar_accomodation_type sat ' .
                'INNER JOIN vi_accomodation_type acctype ON sat.vi_accomodation_type_code = acctype.code ' .
                'WHERE sat.vi_seminar_id = :vi_seminar_id',
                array('vi_seminar_id' => $id));
    }

    public function savePrices() {
        $pricesForm = $this->postToDbValues();
        $id = $pricesForm['id'];

        $viSeminar = $this->getSeminarById($id);
        
        $this->viewData['viSeminar'] = $viSeminar;
        $this->viewData['pricesForm'] = $pricesForm;
        $this->editPricesData($id);

        $currencyCode = constants::$LOCAL_SEMINAR_CURRENCY_CODE;

        $validator = new EntityValidator($pricesForm[$currencyCode], 'viSeminar', $this->msg);
        $validator->decimal('course_fee');
        $validator->decimal('breakfast_price');
        $validator->decimal('lunch_price');
        $validator->decimal('dinner_price');
        $validator->decimalOrNull('social_discount');
        $validator->decimalOrNull('early_discount');
        if ($pricesForm[$currencyCode]['early_discount']) {
        	$validator2 = new EntityValidator($pricesForm, 'viSeminar');
        	$validator2->date('early_discount_deadline');
        }
        else {
        	unset($pricesForm['early_discount_deadline']);
        }
        $validator->decimalOrNull('bank_payment_discount');

        if (! $this->msg->hasErrors()) {
            foreach ($this->viewData['viAccomodationTypeList'] as $viAccomodationType) {
                $price = $pricesForm['viAccomodationTypePrice' . $currencyCode][$viAccomodationType['code']];
                if (valid::isWhite($price)) {
                    $this->msg->error('viseminWizard.error-atMissingPrice', text::get('currency.mark-' . $currencyCode), $viAccomodationType['name']);
                }
            }
            if (! $this->msg->hasErrors()) {
                foreach ($this->viewData['viAccomodationTypeList'] as $viAccomodationType) {
                    $price = $pricesForm['viAccomodationTypePrice' . $currencyCode][$viAccomodationType['code']];
                    if (! valid::isPositiveDecimalOrZero($price)) {
                        $this->msg->error('viseminWizard.error-atPriceNotDecimal', $viAccomodationType['name'], text::get('currency.mark-' . $currencyCode));
                    }
                }
            }
        }
        if ($this->msg->hasErrors()) {
            $this->renderSubview('admin/viSeminar/viSeminarEditPrices');
            return;
        }

        // Save the values in a transaction
        $this->dao->getDb()->beginTransaction();
        try {
            $seminarPrices = $this->dao->getViSeminarPricesByCurrency($id, $currencyCode);
            
            $seminarPrices['course_fee'] = $pricesForm[$currencyCode]['course_fee'];
            $seminarPrices['social_discount'] = $pricesForm[$currencyCode]['social_discount'];
            $seminarPrices['breakfast_price'] = $pricesForm[$currencyCode]['breakfast_price'];
            $seminarPrices['lunch_price'] = $pricesForm[$currencyCode]['lunch_price'];
            $seminarPrices['dinner_price'] = $pricesForm[$currencyCode]['dinner_price'];
            $seminarPrices['bank_payment_discount'] = $pricesForm[$currencyCode]['bank_payment_discount'];
            $seminarPrices['early_discount'] = $pricesForm[$currencyCode]['early_discount'];
            
            $this->dao->update('vi_seminar_price', $seminarPrices, array('vi_seminar_id', 'currency_code'));

            // Save the accomodation types and the prices
            foreach ($this->viewData['viAccomodationTypeList'] as $viAccomodationType) {
                $accomodationPrice = $this->dao->getViSeminarAccomodationPrice($id, $viAccomodationType['code'], $currencyCode);
                $accomodationPrice['price'] = $pricesForm['viAccomodationTypePrice' . $currencyCode][$viAccomodationType['code']];
                $this->dao->update('vi_seminar_accomodation_type_price', $accomodationPrice, array('vi_seminar_id', 'vi_accomodation_type_code', 'currency_code'));
            }

            $viSeminar['first_meal'] = $pricesForm['first_meal'];
            $viSeminar['last_meal'] = $pricesForm['last_meal'];
            $viSeminar['early_discount_deadline'] = $pricesForm['early_discount_deadline'];
            $this->dao->update('vi_seminar', $viSeminar);

            $this->dao->getDb()->commit();
        }
        catch (Exception $exception) {
            // If something raised an exception in our transaction block of statements,
            // roll back any work performed in the transaction
            $this->dao->getDb()->rollBack();

            $exceptionHandler = new ExceptionHandler($this, $exception);
            $exceptionHandler->handle();

            $this->redirect('/admin/viSeminar/display?id=' . $id);
        }

        $this->msg->info('viSeminar.editPricesSuccess', $viSeminar['name']);
        $this->redirect('/admin/viSeminar/display?id=' . $id);
    }

    function edit() {
        $id = $this->input->get('id');
        $viSeminar = array();

        if ($id) {
            $viSeminar = $this->dao->getById('vi_seminar', $id);
            if (! $viSeminar) {
                $this->redirectToList('error.recordNotFound');
            }
            $viSeminar['vi_seminar_tag_ids'] = $this->dao->queryFirstValues(
                    'SELECT vi_seminar_tag_id FROM vi_seminar_tag_asoc WHERE vi_seminar_id = :id', array('id' => $id));
        }
        else {
            $this->redirectToList('error.invalidId');
        }

        $this->viewData['viSeminar'] = $viSeminar;
        $this->loadFormData();
        $this->renderSubview('admin/viSeminar/viSeminarEdit');
    }

    protected function loadFormData() {
        $id = $this->viewData['viSeminar']['id'];
        
        $this->viewData['bankAccountOptions'] = $this->dao->queryOptions(
                'SELECT id, name FROM bank_account ORDER BY name ASC');

        $this->viewData['nameAcValues'] = $this->dao->queryFirstValues('SELECT DISTINCT name FROM vi_seminar ORDER BY name ASC');

        $this->viewData['viSeminarTagOptions'] = $this->dao->queryOptions('SELECT id, name, description FROM vi_seminar_tag');
    }
    
    public function save() {
        $viSeminar = $this->postToDbValues();
        $id = $viSeminar['id'];

        if (! valid::isId($id)) {
            $this->msg->error('error.invalidId');
            $this->redirectToList('error.invalidId');
        }
        
        $this->viewData['viSeminar'] = $viSeminar;

        // Basic validation
        $validator = new EntityValidator($viSeminar, 'viSeminar', $this->msg);
        $validator->required('name');
        $validator->required('instructor');
        $validator->date('begin_date');
        $validator->time('begin_time');
        $validator->date('end_date');
        $validator->time('end_time');
        $validator->required('bank_account_id');
        $validator->required('variable_symbol');
        $validator->required('description');
        $validator->boolean('last_meal_in_package_option');
        $validator->booleanOrNull('active');

        if ($this->msg->hasErrors()) {
            $this->loadFormData();
            $this->renderSubview('admin/viSeminar/viSeminarEdit');
            return;
        }

        // Database validation
        $uniqueColumns = array('name', 'begin_date', 'end_date');
        if ($this->dao->alreadyExists('vi_seminar', $uniqueColumns, $viSeminar)) {
            $this->msg->error('viSeminar.error-alreadyExists');
            $this->loadFormData();
            $this->renderSubview('admin/viSeminar/viSeminarEdit');
            return;
        }

        // Insert or update the seminar
        if ($id != null) {
            $this->dao->update('vi_seminar', $viSeminar, 'id', 'vi_seminar_tag_ids');

            $this->dao->executeQuery('DELETE FROM vi_seminar_tag_asoc WHERE vi_seminar_id = :id', array('id' => $id));
            if ($viSeminar['vi_seminar_tag_ids']) {
                foreach ($viSeminar['vi_seminar_tag_ids'] as $tagId) {
                    $tagAsoc = array(
                        'vi_seminar_id' => $id,
                        'vi_seminar_tag_id' => $tagId
                    );
                    $this->dao->insert('vi_seminar_tag_asoc', $tagAsoc);
                }
            }
            
            $this->msg->info('viSeminar.editSuccess', $viSeminar['name']);
        }
        
        $this->redirect('/admin/viSeminar/display?id=' . $id);
    }
    
    public function delete() {
        $id = $this->input->get('id');
        
        // Basic validation
        $viSeminar = $this->getSeminarById($id);
        
        $this->dao->delete('vi_seminar', $id);
        
        $this->msg->info('viSeminar.deleteSuccess', $viSeminar['name']);
        $this->redirectToList();
    }

    public function billTable() {
        $viSeminarId = $this->input->get('id');

        // Basic validation
        if (! valid::isId($viSeminarId)) {
            $this->redirectToList('error.invalidId');
        }

        $viSeminar = $this->dao->fetchViSeminarWithPrices($viSeminarId);
        if (! $viSeminar) {
            $this->redirectToList('error.recordNotFound');
        }

        $this->mergeViewData($this->billTableData($viSeminar));
        
        $this->renderMySubview('viSeminarBillTable');
    }

    private function selectRegistrationList($viSeminarId, $presentOnly, $orderFromUrl=FALSE, $cancelledOnly = FALSE) {
        $params = array('vi_seminar_id' => $viSeminarId);
        $query = 'SELECT reg.*, ' .
                'per.first_name, per.last_name, per.birth_date, per.email, per.idcard_number, per.street, per.city, per.postal_code, per.notes AS person_notes, per.gender, ' .
                'c.code AS country_code, c.name_native AS country_name, ' .
                'vat.name AS vi_accomodation_type_name ' .
                'FROM vi_registration reg ' .
                'LEFT OUTER JOIN person per ON per.id = reg.person_id ' .
                'LEFT OUTER JOIN country c ON per.country_code = c.code ' .
                'LEFT OUTER JOIN vi_accomodation_type vat ON vat.code = reg.vi_accomodation_type_code ' .
                'WHERE reg.vi_seminar_id = :vi_seminar_id ';
        if (! $cancelledOnly) {
            $query .= 'AND reg.cancelled_at IS NULL ';
        }
        else {
            $query .= 'AND reg.cancelled_at IS NOT NULL ';
        }
        if ($presentOnly) {
            $query .= 'AND reg.is_present = :present ';
            $params['present'] = sql::$TRUE;
        }
        if ($orderFromUrl) {
            $query = $this->orderLimitAndOffset($query);
        }
        else {
            $query .= 'ORDER BY reg.is_instructor ASC, per.last_name, per.first_name';
        }
        return $this->dao->queryAll($query, $params);
    }

    private function billTableData($viSeminar) {
        if (! $viSeminar['prices-CZK']) {
            throw new IncompleteDataException('Prices are missing.');
        }

        $billData = array();
        $billData['viRegistrationList'] = $this->selectRegistrationList($viSeminar['id'], TRUE);
        $amountSums = array('meals_price' => 0, 'accomodation_price' => 0, 'course_fee' => 0, 'discount' => 0, 'total_price' => 0, 'paid_amount' => 0, 'for_village' => 0);
        $sumsWithoutInstructors = array('meals_price' => 0, 'accomodation_price' => 0, 'course_fee' => 0, 'discount' => 0, 'total_price' => 0);
        $nightFeeForVillage = bcadd(Kohana::config('yosemin.village-recreationFee'), Kohana::config('yosemin.village-accomodationFee'), sql::$DECIMAL_PRECISION);
        $usedRooms = array();
        $mealCounts = array();
        foreach ($billData['viRegistrationList'] as &$viRegistration) {
            $viRegistration['discount'] = $viRegistration['social_discount_applied'] ? $viSeminar['prices-' . $viRegistration['currency_code']]['social_discount'] : 0;
            if ($viRegistration['extra_discount']) {
            	$viRegistration['discount'] += $viRegistration['extra_discount'];
            }
            if ($viRegistration['bank_payment_discount']) {
            	$viRegistration['discount'] += $viRegistration['bank_payment_discount'];
            }

            $person = $this->dao->getById('person', $viRegistration['person_id']);

            $viRegistration['for_village'] = 0;
            $viRegistration['removedFromVillagePaymentReason'] = $this->removedFromVillagePaymentReason($viRegistration);
            if (! $viRegistration['removedFromVillagePaymentReason']) {
                // If the person is accomodated on the floor, we do not pay for accomodating him
                if ($viRegistration['vi_accomodation_type_code'] == 'hala_spacak') {
                    $viRegistration['for_village'] = bcmul(Kohana::config('yosemin.village-recreationFee'), $viRegistration['night_count'], sql::$DECIMAL_PRECISION);
                }
                else {
                    $viRegistration['for_village'] = bcmul($nightFeeForVillage, $viRegistration['night_count'], sql::$DECIMAL_PRECISION);
                }
            }

            foreach ($amountSums as $field => $value) {
                $amountSums[$field] = bcadd($amountSums[$field], $viRegistration[$field], sql::$DECIMAL_PRECISION);
            }
            if (! $viRegistration['is_instructor']) {
                foreach ($sumsWithoutInstructors as $field => $value) {
                    $sumsWithoutInstructors[$field] = bcadd($sumsWithoutInstructors[$field], $viRegistration[$field], sql::$DECIMAL_PRECISION);
                }
            }
            $viRegistration['mealSelection'] = $this->selectMealSelection($viRegistration['id']);
            foreach ($viRegistration['mealSelection'] as $mealCode) {
                if (! $mealCounts[$mealCode]) {
                    $mealCounts[$mealCode] = 0;
                }
                $mealCounts[$mealCode] += 1;
            }
            $viRegistration['paymentList'] = $this->dao->getByField('vi_registration_payment', 'vi_registration_id', $viRegistration['id'], 'payment_date ASC, payment_time ASC');
            $paidAmount = 0;
            foreach ($viRegistration['paymentList'] as $payment) {
                $paidAmount = bcadd($paidAmount, $payment['paid_amount'], sql::$DECIMAL_PRECISION);
            }
            $viRegistration['paid_price'] = $paidAmount;
            $amountSums['paid_amount'] = bcadd($amountSums['paid_amount'], $paidAmount, sql::$DECIMAL_PRECISION);
            
            if ($viRegistration['room_number'] && preg_match('/^postel_.*/', $viRegistration['vi_accomodation_type_code'])) {
            	if (! $usedRooms[$viRegistration['room_number']]) {
            		$usedRooms[$viRegistration['room_number']] = 0;
            	}
            	$usedRooms[$viRegistration['room_number']] += 1;
            }
        }

        $viSeminar['dayCount'] = seminarHelper::dayCount($viSeminar);

        $billData['usedRoomList'] = $usedRooms;
        $billData['mealCounts'] = $mealCounts;
        $billData['viSeminar'] = $viSeminar;
        $billData['currencyCodeList'] = $this->dao->selectViSeminarCurrecyCodeList($viSeminar['id']);
        $billData['amountSums'] = $amountSums;
        $billData['sumsWithoutInstructors'] = $sumsWithoutInstructors;
        return $billData;
    }

    private function selectMealSelection($viRegistrationId) {
        $mealList = $this->dao->getByField('vi_registration_meal', 'vi_registration_id', $viRegistrationId);
        $mealSelection = array();
        foreach ($mealList as $meal) {
            $mealCode = $meal['day'] . '_' . $meal['meal'];
            $mealSelection[] = $mealCode;
        }
        return $mealSelection;
    }

    public function billTableToExcel() {
        $viSeminarId = $this->input->get('id');

        // Basic validation
        if (! valid::isId($viSeminarId)) {
            $this->redirectToList('error.invalidId');
        }

        $viSeminar = $this->dao->fetchViSeminarWithPrices($viSeminarId);
        if (! $viSeminar) {
            $this->redirectToList('error.recordNotFound');
        }

        $billData = $this->billTableData($viSeminar);
        $viSeminar = $billData['viSeminar'];
        $viRegistrationList = $billData['viRegistrationList'];
        $currencyCodeList = $billData['currencyCodeList'];
        $sumsWithoutInstructors = $billData['sumsWithoutInstructors'];
        $amountSums = $billData['amountSums'];

        $currencyCode = 'CZK';

        excel::loadClasses();

        $objPHPExcel = new PHPExcel();

        $headerStyle = array(
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );

        $dataStyle = array(
            'font' => array(
                'bold' => false,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
                'inside' => array(
                    'style' => PHPExcel_Style_Border::BORDER_HAIR,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
        );

        $firstInstructorStyle = array(
            'font' => array(
                'bold' => false,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
                'top' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF808080'),
                ),
            ),
        );

        $summaryStyle = array(
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            ),
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array('argb' => 'FFFFFFD0'),
            ),
        );

        $titleStyle = array(
            'font' => array(
                'bold' => true,
                'size' => 20,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
            ),
        );

        $variableSymbolStyle = array(
            'font' => array(
                'bold' => true,
                'size' => 14,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                'wrap' => FALSE
            ),
        );

        $summaryRowNameStyle = array(
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
            ),
        );


        // Metadata
        excel::setMetadata($objPHPExcel);
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();

        $columnLetters = excel::columnLetters();

        $rowNumber = 1;
        $columnNumber = 0;

        /*---------------------------------
         * Headers
         *---------------------------------*/

        $col = 'C';
        $sheet->setCellValue($col . $rowNumber, $viSeminar['name'] . ', ' . format::date($viSeminar['begin_date']) . ' - ' . format::date($viSeminar['end_date']));
        $sheet->getStyle($col . $rowNumber)->applyFromArray($titleStyle);
        
        $rowNumber ++;
        $sheet->setCellValue($col . $rowNumber, text::get('viSeminar.variable_symbol') . ': ' . $viSeminar['variable_symbol']);
        $sheet->getStyle($col . $rowNumber)->applyFromArray($variableSymbolStyle);
        $rowNumber += 2;

        $columnKeys = array('viRegistration.id-short', 'viRegistration.room_number', 'person.full_name');
        foreach ($columnKeys as $key) {
            $col = $columnLetters[$columnNumber++];
            $sheet->mergeCells($col . $rowNumber . ':' . $col . ($rowNumber + 1));
            $sheet->setCellValue($col . $rowNumber, text::get($key));
        }
        // Meals
        for ($dayIndex = 0; $dayIndex < $viSeminar['dayCount']; $dayIndex++) {
            $col = $columnLetters[$columnNumber];
            $date = date::addDays($viSeminar['begin_date'], $dayIndex);
            $sheet->mergeCells($col . $rowNumber . ':' . ($columnLetters[$columnNumber + 2]) . $rowNumber);
            $sheet->setCellValue($col . $rowNumber, format::date($date));
            // Meal names
            for ($i = 0; $i < count(registrationHelper::$MEAL_LIST); $i++) {
                $mealName = text::get('viSeminar.' . registrationHelper::$MEAL_LIST[$i]);
                $sheet->setCellValue($columnLetters[$columnNumber++] . ($rowNumber + 1), substr($mealName, 0, 1));
            }
        }
        // Price for each meal
        foreach (registrationHelper::$MEAL_LIST as $meal) {
            $col = $columnLetters[$columnNumber++];
            $sheet->mergeCells($col . $rowNumber . ':' . $col . ($rowNumber + 1));
            $sheet->setCellValue($col . $rowNumber, substr(text::get('viSeminar.' . $meal), 0, 2));
        }
        // Total prices for meals, accomodation and course fee
        $columnKeys = array(
            'meals_price',
            'accomodation_price',
            'course_fee',
            'discounts_fee',
            'total_price',
            'paid_price',
            'for_village',
            'accounting_record_numbers',
            'notes'
        );
        foreach ($columnKeys as $key) {
            $col = $columnLetters[$columnNumber++];
            $sheet->mergeCells($col . $rowNumber . ':' . $col . ($rowNumber + 1));
            $sheet->setCellValue($col . $rowNumber, text::get('viRegistration.' . $key));
        }
        
        $lastColumn = $columnLetters[$columnNumber];

        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                break;
            }
            $sheet->getStyle($column . $rowNumber .':' . $column . ($rowNumber + 1))->applyFromArray($headerStyle);
        }

        $rowNumber += 2;

        /*---------------------------------
         * Data
         *---------------------------------*/

        $firstDataRow = $rowNumber;

        $instructorPart = false;
        $instructorCount = 0;
        $bankPaymentSum = 0;
        foreach ($viRegistrationList as $viRegistration) {
            $columnNumber = 0;
            if (! $instructorPart && $viRegistration['is_instructor']) {
                $instructorPart = true;
                $firstInstructorRow = $rowNumber;
                // TODO add border
                // echo '<tr class="instructorBlockSeparator">';
            }
            if ($instructorPart) {
                $instructorCount ++;
            }
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['id']);
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['room_number']);
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['last_name'] . ' ' . $viRegistration['first_name']);
            
            // Meals
            $firstMealColumn = $columnNumber;
            for ($dayIndex = 0; $dayIndex < $viSeminar['dayCount']; $dayIndex++) {
            	$date = format::isoDate(date::addDays($viSeminar['begin_date'], $dayIndex));
                foreach (registrationHelper::$MEAL_LIST as $meal) {
                    if (registrationHelper::mealTableEmptyField($viSeminar, $dayIndex, $meal)) {
                        $columnNumber++;
                        continue;
                    }

                    $mealCode = $date . '_' . $meal;
                    if (array_search($mealCode, $viRegistration['mealSelection']) !== FALSE) {
                        $sheet->setCellValue($columnLetters[$columnNumber] . $rowNumber, 1);
                    }
                    $columnNumber++;
                }
            }

            $firstMealTypePriceColumn = $columnNumber;
            foreach (registrationHelper::$MEAL_LIST as $meal) {
                $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber,
                        format::decimal($viSeminar['prices-' . $currencyCode][$meal . '_price']));
            }

            // Make meal total price as a formula
            $mealPriceFormula = '=';
            for ($dayIndex = 0; $dayIndex < $viSeminar['dayCount']; $dayIndex++) {
                for ($mealIndex = 0; $mealIndex < 3; $mealIndex++) {
                    $mealPriceFormula .= $columnLetters[$firstMealColumn + $mealIndex + 3 * $dayIndex] . $rowNumber . '*' . 
                            $columnLetters[$firstMealTypePriceColumn + $mealIndex] . $rowNumber . '+';
                }
            }
            $mealPriceFormula = substr($mealPriceFormula, 0, -1);
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $mealPriceFormula);

            $priceColumnList = array('accomodation_price', 'course_fee', 'discount');
            foreach ($priceColumnList as $priceColumn) {
                $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber,
                        format::decimal($viRegistration[$priceColumn]));
            }

            // Make total price as a formula (meals + accomodation + courseFee - discounts)
            $totalPriceFormula = '=' . $columnLetters[$columnNumber - 4] . $rowNumber . '+' .
                    $columnLetters[$columnNumber - 3] . $rowNumber . '+' .
                    $columnLetters[$columnNumber - 2] . $rowNumber . '-' .
                    $columnLetters[$columnNumber - 1] . $rowNumber;
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $totalPriceFormula);

            if ($viRegistration['is_instructor']) {
                $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, text::get('viSeminar.honorarium-short'));
            }
            else {
                $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['paid_price']);
            }
            
            // Village
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['for_village']);

            $payments = '';
            for ($i = 0; $i < count($viRegistration['paymentList']); $i++) {
                $payment = $viRegistration['paymentList'][$i];

                if (registrationHelper::isBankPayment($payment['record_number'])) {
                    $bankPaymentSum = bcadd($bankPaymentSum, $payment['paid_amount'], sql::$DECIMAL_PRECISION);
                }

                $payments .= $payment['record_number'] ? $payment['record_number'] : $payment['paid_amount'];
                if ($i < count($viRegistration['paymentList']) - 1) {
                    $payments .= ', ';
                }
            }
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $payments);
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['notes']);
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['removedFromVillagePaymentReason']);
            $rowNumber++;
        }

        /*---------------------------------
         * Styling
         *---------------------------------*/

        // Column widths
        $numberOfMeals = 9; // $viSeminar['dayCount'] * count(registrationHelper::$MEAL_LIST);
        $columnWidths = array(8, 11.5, 22);
        for ($i = 0; $i < $numberOfMeals; $i++) {
            $columnWidths[] = 3.2;
        }
        $columnWidths = array_merge($columnWidths, array(3.3, 3.3, 3.3, 13.5, 16, 11, 10.5, 14.5, 11, 8.5, 18, 30));
        foreach ($columnWidths as $columnNumber => $width) {
            $sheet->getColumnDimension($columnLetters[$columnNumber])->setWidth($width);
        }

        // Data column borders
        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                break;
            }
            //- $sheet->getStyle($column . $firstDataRow . ':' . $column . ($rowNumber - 1))->applyFromArray($dataStyle);
        }
        //- $sheet->getStyle($column . $firstDataRow . ':' . $lastColumn . ($rowNumber - 1))->applyFromArray($dataStyle);

        // Separator between ordinary participants and instructors
        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                break;
            }
            //- $sheet->getStyle($column . $firstInstructorRow)->applyFromArray($firstInstructorStyle);
        }
        //- $sheet->getStyle($column . $firstInstructorRow . ':' . $lastColumn . $firstInstructorRow)->applyFromArray($firstInstructorStyle);

        /*---------------------------------
         * Summary
         *---------------------------------*/

        $columnNumber = 3;
        $rowNumber++;
        
        $sheet->mergeCells('A' . $rowNumber . ':' . 'C' . $rowNumber);
        $sheet->setCellValue('A' . $rowNumber, text::get('viSeminar.billSumsTotal') . ':');
        $sheet->getStyle('A' . $rowNumber)->applyFromArray($summaryRowNameStyle);

        for ($dayIndex = 0; $dayIndex < $viSeminar['dayCount']; $dayIndex++) {
        	$date = format::isoDate(date::addDays($viSeminar['begin_date'], $dayIndex));
            foreach (registrationHelper::$MEAL_LIST as $meal) {
                $mealCode = $date . '_' . $meal;
                $col = $columnLetters[$columnNumber++];
                $sheet->setCellValue($col . $rowNumber, $mealCounts[$mealCode]);
                $sum = 'SUM(' . $col . $firstDataRow . ':' . $col . ($rowNumber - 2) . ')';
                $sheet->setCellValue($col . $rowNumber, '=IF(' . $sum . '=0,"",' . $sum . ')');
                $sheet->getStyle($col . $rowNumber)->applyFromArray($summaryStyle);
            }
        }

        $columnNumber += 3;

        foreach ($amountSums as $field => $value) {
            $col = $columnLetters[$columnNumber++];
            $sheet->setCellValue($col . $rowNumber, '=SUM(' . $col . $firstDataRow . ':' . $col . ($rowNumber - 2) . ')');
            $sheet->getStyle($col . $rowNumber)->applyFromArray($summaryStyle);
        }

        // Sum of course fees and discounts
        $rowNumber++;
        $columnNumber = 17;
        $col = $columnLetters[$columnNumber];
        $sheet->setCellValue($col . $rowNumber, '=' . $col . ($rowNumber - 1) . '-' . $columnLetters[$columnNumber + 1] . ($rowNumber - 1));
        $sheet->mergeCells($col . $rowNumber . ':' . $columnLetters[$columnNumber + 1] . $rowNumber);
        $sheet->getStyle($col . $rowNumber . ':' . $columnLetters[$columnNumber + 1] . $rowNumber)->applyFromArray($summaryStyle);

        // Sums without instructors
        $columnNumber = 3;
        $rowNumber ++;

        $sheet->mergeCells('A' . $rowNumber . ':' . 'C' . $rowNumber);
        $sheet->setCellValue('A' . $rowNumber, text::get('viSeminar.billSumsWithoutInstructors') . ':');
        $sheet->getStyle('A' . $rowNumber)->applyFromArray($summaryRowNameStyle);

        $columnNumber += 3 + ($viSeminar['dayCount'] * count(registrationHelper::$MEAL_LIST));
        foreach ($sumsWithoutInstructors as $field => $value) {
            $col = $columnLetters[$columnNumber++];
            if ($field == 'course_fee' || $field == 'discount') {
                continue;
            }
            $sheet->setCellValue($col . $rowNumber, '=SUM(' . $col . $firstDataRow . ':' . $col . ($rowNumber - $instructorCount - 4) . ')');
            $sheet->getStyle($col . $rowNumber)->applyFromArray($summaryStyle);
        }

        // Bank payments sum
        $rowNumber += 2;
        $columnNumber = 19;

        $col = $columnLetters[$columnNumber++];
        $sheet->setCellValue($col . $rowNumber, text::get('viSeminar.bankPaymentSum') . ':');
        $sheet->getStyle($col . $rowNumber . ':' . $col . ($rowNumber + 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $sheet->setCellValue($col . ($rowNumber + 1), text::get('viSeminar.cashPaymentSum') . ':');

        $col = $columnLetters[$columnNumber];
        $sheet->setCellValue($col . $rowNumber, $bankPaymentSum);
        $sheet->setCellValue($col . ($rowNumber + 1), '=' . $col . ($rowNumber - 4) . '-' . $col . $rowNumber);

        // Used room count
        $columnNumber = 1;
        $col = $columnLetters[$columnNumber];
        $roomNumberString = '';
        foreach ($billData['usedRoomList'] as $roomNumber => $pocet) {
        	$roomNumberString .= $roomNumber . '/' . $pocet . ', ';
        }
        $roomNumberString = substr($roomNumberString, 0, -2);
        $sheet->setCellValue($col . $rowNumber, 'Počet pokojů: ' . count($billData['usedRoomList']) . ' - ' . $roomNumberString); 
        
        // Fees for village
        $sheetWithFees = $objPHPExcel->createSheet();
        $this->villageFeesExcelTable($viSeminar, $sheetWithFees);
        $objPHPExcel->setActiveSheetIndex(0);

        // Save the file
        $fileName = tempNam(Kohana::config('yosemin.temp_dir'), 'yoseminXlsx');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($fileName);

        download::force($fileName, NULL, 'VS' . $viSeminar['variable_symbol'] . ' ' . $viSeminar['name'] . '.xlsx');
    }

    public function viRegistrationList() {
        $viSeminarId = $this->input->get('id');

        if (! valid::isId($viSeminarId)) {
            $this->redirectToList('error.invalidId');
        }

        $viSeminar = $this->dao->getById('vi_seminar', $viSeminarId);
        if (! $viSeminar) {
            $this->redirectToList('error.recordNotFound');
        }

        $this->viewData['viRegistrationList'] = $this->selectRegistrationList($viSeminarId, FALSE);
        foreach ($this->viewData['viRegistrationList'] as &$viRegistration) {
            $viRegistration['paymentList'] = $this->dao->getByField('vi_registration_payment', 'vi_registration_id', $viRegistration['id'], 'payment_date ASC, payment_time ASC');
        }

        // Cancelled registrations
        $this->viewData['cancelledRegistrationList'] = $this->selectRegistrationList($viSeminarId, FALSE, FALSE, TRUE);
        foreach ($this->viewData['cancelledRegistrationList'] as &$viRegistration) {
            $viRegistration['paymentList'] = $this->dao->getByField('vi_registration_payment', 'vi_registration_id', $viRegistration['id'], 'payment_date ASC, payment_time ASC');
        }

        $viSeminar['dayCount'] = seminarHelper::dayCount($viSeminar);

        $this->viewData['viSeminar'] = $viSeminar;
        $this->renderMySubview('viSeminarRegistrationList');
    }

    public function mealTableForKitchen() {
        $viSeminarId = $this->input->get('id');
        $mode = $this->input->get('mode');

        if (! valid::isId($viSeminarId)) {
            $this->redirectToList('error.invalidId');
        }

        $viSeminar = $this->dao->getById('vi_seminar', $viSeminarId);
        if (! $viSeminar) {
            $this->redirectToList('error.recordNotFound');
        }

        $viSeminar['dayCount'] = seminarHelper::dayCount($viSeminar);

        $queryParams = array('vi_seminar_id' => $viSeminarId);
        $query = 'SELECT reg.*, per.first_name, per.last_name ' .
                'FROM vi_registration reg ' .
                'LEFT OUTER JOIN person per ON per.id = reg.person_id ' .
                'WHERE reg.vi_seminar_id = :vi_seminar_id ' .
                'AND reg.cancelled_at IS NULL ';
        if ($mode == 'present') {
            $queryParams['is_present'] = sql::$TRUE;
            $query .= 'AND reg.is_present = :is_present ';
        }
        $query .= 'ORDER BY per.last_name, per.first_name';
        $viRegistrationList = $this->dao->queryAll($query, $queryParams);
        foreach ($viRegistrationList as &$viRegRef) {
            $viRegRef['mealSelection'] = $this->selectMealSelection($viRegRef['id']);
        }

        $currencyCode = 'CZK';

        excel::loadClasses();

        $objPHPExcel = new PHPExcel();

        $titleStyle = array(
            'font' => array(
                'bold' => FALSE,
                'size' => 14,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                'wrap' => FALSE,
            ),
        );

        $headerStyle = array(
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
            'font' => array(
                'bold' => TRUE,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );

        $dataStyle = array(
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
                'inside' => array(
                    'style' => PHPExcel_Style_Border::BORDER_HAIR,
                    'color' => array('argb' => 'FF606060'),
                ),
            ),
        );

        $dataCellStyle = array(
            'font' => array(
                'bold' => FALSE,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );

        $nameStyle = $dataCellStyle;
        $nameStyle['alignment'] = array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        );

        $summaryStyle = array(
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array('argb' => 'FFFFFFD0'),
            ),
        );

        $summaryRowTitleStyle = array(
            'font' => array(
                'bold' => TRUE,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );

        // Metadata
        $objPHPExcel->getProperties()->setCreator("Sdružení Jóga v denním životě Střílky");
        $objPHPExcel->getProperties()->setLastModifiedBy("Sdružení Jóga v denním životě Střílky");
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();

        $columnLetters = excel::columnLetters();

        $rowNumber = 1;
        $columnNumber = 0;

        /*---------------------------------
         * Headers
         *---------------------------------*/

        $sheet->setCellValue('A' . $rowNumber, $viSeminar['name'] . ', ' . format::date($viSeminar['begin_date']) . ' - ' . format::date($viSeminar['end_date']));
        $sheet->mergeCells('A' . $rowNumber . ':' . 'J' . $rowNumber);
        $sheet->getStyle('A' . $rowNumber)->applyFromArray($titleStyle);
        $rowNumber += 2;

        $col = $columnLetters[$columnNumber++];
        $sheet->mergeCells($col . $rowNumber . ':' . $col . ($rowNumber + 1));
        $sheet->setCellValue($col . $rowNumber, text::get('person.full_name'));
        
        // Meals
        for ($dayIndex = 0; $dayIndex < $viSeminar['dayCount']; $dayIndex++) {
            $col = $columnLetters[$columnNumber];
            $date = date::addDays($viSeminar['begin_date'], $dayIndex);
            $sheet->mergeCells($col . $rowNumber . ':' . ($columnLetters[$columnNumber + 2]) . $rowNumber);
            $sheet->setCellValue($col . $rowNumber, format::date($date));
            $sheet->setCellValue($col . $rowNumber, text::get('application.dayOfWeek-' . $date->format('N')));
            // Meal names
            for ($i = 0; $i < count(registrationHelper::$MEAL_LIST); $i++) {
                $mealName = text::get('viSeminar.' . registrationHelper::$MEAL_LIST[$i]);
                $sheet->setCellValue($columnLetters[$columnNumber++] . ($rowNumber + 1), substr($mealName, 0, 1));
            }
        }

        $lastColumn = $columnLetters[$columnNumber];

        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                break;
            }
            $sheet->getStyle($column . $rowNumber .':' . $column . ($rowNumber + 1))->applyFromArray($headerStyle);
            $sheet->getStyle($column . ($rowNumber + 1) .':' . $column . ($rowNumber + 2))->applyFromArray($headerStyle);
        }

        $rowNumber += 2;

        /*---------------------------------
         * Data
         *---------------------------------*/

        $firstDataRow = $rowNumber;

        foreach ($viRegistrationList as $viRegistration) {
            $columnNumber = 0;

            $col = $columnLetters[$columnNumber++];
            $sheet->setCellValue($col . $rowNumber, $viRegistration['last_name'] . ' ' . $viRegistration['first_name']);
            $sheet->getStyle($col . $rowNumber)->applyFromArray($nameStyle);

            // Meals
            for ($dayIndex = 0; $dayIndex < $viSeminar['dayCount']; $dayIndex++) {
                $date = format::isoDate(date::addDays($viSeminar['begin_date'], $dayIndex));
                foreach (registrationHelper::$MEAL_LIST as $meal) {
                    $col = $columnLetters[$columnNumber];
                    $sheet->getStyle($col . $rowNumber)->applyFromArray($dataCellStyle);
                    if (registrationHelper::mealTableEmptyField($viSeminar, $dayIndex, $meal)) {
                        $columnNumber++;
                        continue;
                    }

                    $mealCode = $date . '_' . $meal;
                    if (array_search($mealCode, $viRegistration['mealSelection']) !== FALSE) {
                        $value = 1;
                        if ($dayIndex == $viSeminar['dayCount'] - 1 && $meal == $viSeminar['last_meal'] && $viRegistration['last_meal_in_package']) {
                            $value = 'Bal';
                        }
                        $sheet->setCellValue($col . $rowNumber, $value);
                        $sheet->getStyle($col . $rowNumber)->applyFromArray($dataCellStyle);
                    }
                    $columnNumber++;
                }
            }

            $rowNumber++;
        }

        /*---------------------------------
         * Summary
         *---------------------------------*/

        $columnNumber = 1;
        $rowNumber++;

        $sheet->setCellValue('A' . $rowNumber, text::get('viSeminar.kitchen-mealCount') . ':');
        $sheet->getStyle('A' . $rowNumber)->applyFromArray($summaryRowTitleStyle);
        
        for ($dayIndex = 0; $dayIndex < $viSeminar['dayCount']; $dayIndex++) {
            $date = format::isoDate(date::addDays($viSeminar['begin_date'], $dayIndex));
            foreach (registrationHelper::$MEAL_LIST as $meal) {
                $mealCode = $date . '_' . $meal;
                $col = $columnLetters[$columnNumber++];
                $sheet->setCellValue($col . $rowNumber, '=SUM(' . $col . $firstDataRow . ':' . $col . ($rowNumber - 1) . ')');
                $sheet->getStyle($col . $rowNumber)->applyFromArray($summaryStyle);
                if ($dayIndex == $viSeminar['dayCount'] - 1 && $meal == $viSeminar['last_meal']) {
                    $lastMealColumn = $col;
                }
            }
        }
        $rowNumber++;

        $sheet->setCellValue('A' . $rowNumber, text::get('viSeminar.kitchen-packageCount') . ':');
        $sheet->getStyle('A' . $rowNumber)->applyFromArray($summaryRowTitleStyle);

        $sheet->setCellValue($lastMealColumn . $rowNumber, '=COUNTIF(' . $lastMealColumn . $firstDataRow . ':' . $lastMealColumn . ($rowNumber - 2) . ',"Bal")');
        $sheet->getStyle($lastMealColumn . $rowNumber)->applyFromArray($summaryStyle);

        /*---------------------------------
         * Styling
         *---------------------------------*/

        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                break;
            }
            $width = 4;
            if ($column == 'A') {
                $width = 30;
            }
            $sheet->getColumnDimension($column)->setWidth($width);
        }

        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                break;
            }
            $sheet->getStyle($column . $firstDataRow .':' . $column . ($rowNumber - 3))->applyFromArray($dataStyle);
        }

        // Save the file
        $fileName = tempNam(Kohana::config('yosemin.temp_dir'), 'yoseminXlsx');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($fileName);

        download::force($fileName, NULL, 'export.xlsx');
    }

    public function cashPaymentsRecordNumberForm() {
        $viSeminarId = $this->input->get('id');

        if (! valid::isId($viSeminarId)) {
            $this->redirectToList('error.invalidId');
        }

        $viSeminar = $this->dao->getById('vi_seminar', $viSeminarId);
        if (! $viSeminar) {
            $this->redirectToList('error.recordNotFound');
        }
        $formData = array('vi_seminar_id' => $viSeminarId);
        $this->viewData['viSeminar'] = $viSeminar;
        $this->viewData['formData'] = $formData;
        $this->renderMySubview('cashPaymentRecordNumber');
    }

    public function cashPaymentsRecordNumber() {
        $formData = $this->postToDbValues();
        $this->viewData['formData'] = $formData;

        $viSeminarId = $formData['vi_seminar_id'];
        $viSeminar = $this->dao->getById('vi_seminar', $viSeminarId);
        if (! $viSeminar) {
            $this->redirectToList('error.recordNotFound');
        }
        $this->viewData['viSeminar'] = $viSeminar;

        $recordNumber = $this->viewData['cash_record_number'];
        if (valid::isEmpty($recordNumber)) {
            $this->msg->error('error.required', 'viSeminar.cash_record_number');
            $this->publishErrors('cashPaymentRecordNumber');
        }

        $paymentList = $this->dao->queryAll("SELECT id FROM vi_registration_payment WHERE record_number IS NULL OR record_number = ''");
        $paymentCount = 0;
        foreach ($paymentList as $payment) {
            $paymentToUpdate = $this->dao->getById('vi_registration_payment', $payment['id']);
            $paymentToUpdate['record_number'] = $recordNumber;
            $this->dao->update('vi_registration_payment', $paymentToUpdate);
            $paymentCount++;
        }
        $this->msg->info('viSeminar.cashPaymentsRecordNumber-success', $recordNumber, $paymentCount);
    }

    private function villageFeesExcelTable(&$viSeminar, &$sheet) {
        $seminarEnd = strtotime($viSeminar['end_date']);
        $date18YearsAgo = (date('Y', $seminarEnd) - 18) . date('-m-d', $seminarEnd);
        $date70YearsAgo = (date('Y', $seminarEnd) - 70) . date('-m-d', $seminarEnd);

        $viRegistrationList = $this->dao->queryAll(
                'SELECT reg.*, per.first_name, per.last_name, per.other_names, per.title, ' .
                'per.birth_date, per.street, per.city, per.idcard_number ' .
                'FROM vi_registration reg ' .
                'LEFT OUTER JOIN person per ON per.id = reg.person_id ' .
                'WHERE reg.vi_seminar_id = :vi_seminar_id ' .
                'AND reg.is_present = :is_present ' .
                'AND reg.cancelled_at IS NULL ' .
                'AND reg.night_count > 0 ' .
                'AND per.birth_date < :birth_date_lt ' .
                'AND per.birth_date > :birth_date_gt ' .
                'AND (per.is_ztp IS NULL OR per.is_ztp = :is_ztp) ' .
                'AND reg.vi_accomodation_type_code != :acc_code ' .
                'ORDER BY per.last_name, per.first_name',
                array(
                    'is_present' => sql::$TRUE,
                    'vi_seminar_id' => $viSeminar['id'],
                    'acc_code' => 'bez_ubytovani',
                    'birth_date_lt' => $date18YearsAgo,
                    'birth_date_gt' => $date70YearsAgo,
                    'is_ztp' => sql::$FALSE
                ));

        $titleStyle = array(
            'font' => array(
                'bold' => FALSE,
                'size' => 14,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
                'wrap' => FALSE,
            ),
        );

        $headerStyle = array(
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
            'font' => array(
                'bold' => TRUE,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );

        $dataStyle = array(
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
                'inside' => array(
                    'style' => PHPExcel_Style_Border::BORDER_HAIR,
                    'color' => array('argb' => 'FF606060'),
                ),
            ),
        );

        $dataCellStyle = array(
            'font' => array(
                'bold' => FALSE,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );

        $nameStyle = $dataCellStyle;
        $nameStyle['alignment'] = array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
        );

        $summaryStyle = array(
            'font' => array(
                'bold' => true,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
            'borders' => array(
                'outline' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('argb' => 'FF000000'),
                ),
            ),
            'fill' => array(
                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                'startcolor' => array('argb' => 'FFFFFFD0'),
            ),
        );

        $summaryRowTitleStyle = array(
            'font' => array(
                'bold' => TRUE,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            ),
        );

        $columnLetters = excel::columnLetters();

        $rowNumber = 1;
        $columnNumber = 0;

        /*---------------------------------
         * Headers
         *---------------------------------*/

        $sheet->setCellValue('A' . $rowNumber, $viSeminar['name'] . ', ' . format::date($viSeminar['begin_date']) . ' - ' . format::date($viSeminar['end_date']));
        $sheet->mergeCells('A' . $rowNumber . ':' . 'H' . $rowNumber);
        $sheet->getStyle('A' . $rowNumber)->applyFromArray($titleStyle);
        $rowNumber += 2;

        $headerKeyList = array('person.full_name', 'person.place_of_residence',
                'person.idcard_number', 'viRegistration.night_count',
                'viSeminar.recreationFee', 'viSeminar.accomodationFee');
        foreach ($headerKeyList as $headerKey) {
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, text::get($headerKey));
        }

        $lastColumn = $columnLetters[$columnNumber];

        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                break;
            }
            $sheet->getStyle($column . $rowNumber)->applyFromArray($headerStyle);
        }

        $rowNumber++;

        /*---------------------------------
         * Data
         *---------------------------------*/

        $firstDataRow = $rowNumber;

        foreach ($viRegistrationList as $viRegistration) {
            $this->writeVillageRecord($sheet, $viRegistration, $rowNumber, $columnLetters, $dataCellStyle, $nameStyle, $lastColumn);
            $rowNumber++;
        }

        /*---------------------------------
         * Summary
         *---------------------------------*/

        $rowNumber++;
        $columnNumber = 2;


        $sheet->setCellValue('A' . $rowNumber, text::get('viSeminar.villageFees-sumTotal') . ':');
        $sheet->mergeCells('A' . $rowNumber . ':' . $columnLetters[$columnNumber] . $rowNumber);
        $sheet->getStyle('A' . $rowNumber)->applyFromArray($summaryRowTitleStyle);
        
        $columnNumber++;
        for ($i = 0; $i < 3; $i++) {
            $col = $columnLetters[$columnNumber++];
            $sheet->setCellValue($col . $rowNumber, '=SUM(' . $col . $firstDataRow . ':' . $col . ($rowNumber - 2) . ')');
            $sheet->getStyle($col . $rowNumber)->applyFromArray($summaryStyle);
        }

        /*---------------------------------
         * Styling
         *---------------------------------*/

        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                break;
            }
            $sheet->getStyle($column . $firstDataRow .':' . $column . ($rowNumber - 2))->applyFromArray($dataStyle);
        }

        $columnWidths = array(24, 16, 42, 14, 12, 20, 20);
        foreach ($columnWidths as $columnNumber => $width) {
            $sheet->getColumnDimension($columnLetters[$columnNumber])->setWidth($width);
        }

        // Set the worksheet’s page orientation and size can be done using the following lines
        $sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

        // Excluded people
        $othersList = $this->dao->queryAll(
        		'SELECT reg.*, per.first_name, per.last_name, per.other_names, per.title, ' .
        		'per.birth_date, per.street, per.city, per.idcard_number ' .
        		'FROM vi_registration reg ' .
        		'LEFT OUTER JOIN person per ON per.id = reg.person_id ' .
        		'WHERE reg.vi_seminar_id = :vi_seminar_id ' .
        		'AND reg.is_present = :is_present ' .
        		'AND reg.cancelled_at IS NULL ' .
        		'AND (reg.night_count = 0 ' .
        		'OR per.birth_date >= :birth_date_gt ' .
        		'OR per.birth_date <= :birth_date_lt ' .
        		'OR per.birth_date IS NULL ' .
        		'OR per.is_ztp = :is_ztp ' .
        		'OR reg.vi_accomodation_type_code = :acc_code)' .
        		'ORDER BY per.last_name, per.first_name',
        		array(
        				'is_present' => sql::$TRUE,
        				'vi_seminar_id' => $viSeminar['id'],
        				'acc_code' => 'bez_ubytovani',
        				'birth_date_gt' => $date18YearsAgo,
        				'birth_date_lt' => $date70YearsAgo,
        				'is_ztp' => sql::$TRUE
        		)
        );
        
        $rowNumber += 2;
        $sheet->setCellValue('A' . $rowNumber, 'Lidé vyřazeni z poplatků pro obec:');
        $rowNumber++;
        foreach ($othersList as $viRegistration) {
        	$this->writeVillageRecord($sheet, $viRegistration, $rowNumber, $columnLetters, $dataCellStyle, $nameStyle, $lastColumn, true);
        	$rowNumber++;
        }
        
        return;
    }
    
    private function removedFromVillagePaymentReason($viRegistration) {
    	$reason = null;
    	if ($viRegistration['is_ztp']) {
    		$reason = 'ZTP';
    	}
    	else if ($viRegistration['night_count'] == 0) {
    		$reason = 'Nula nocí';
    	}
    	else if ($viRegistration['vi_accomodation_type_code'] == 'bez_ubytovani') {
    		$reason = 'Bez ubytování';
    	}
    	else if (date::personAge($viRegistration['birth_date']) < 18) {
    		$reason = 'Mladší 18 let';
    	}
    	else if (date::personAge($viRegistration['birth_date']) > 70) {
    		$reason = 'Starší 70 let';
    	}
    	else if (! $viRegistration['birth_date']) {
    		$reason = 'Bez data narození';
    	}
    	return $reason;
    }

    private function writeVillageRecord($sheet, $viRegistration, $rowNumber, $columnLetters, $dataCellStyle, $nameStyle, $lastColumn, $writeReason=false) {
        $columnNumber = 0;

        $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, format::personFullName($viRegistration, TRUE));

        // $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, format::date($viRegistration['birth_date']));
        $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, format::address($viRegistration, 'CZ'));
        $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['idcard_number']);
        $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $viRegistration['night_count']);

        $recreationFee = bcmul(Kohana::config('yosemin.village-recreationFee'), $viRegistration['night_count'], sql::$DECIMAL_PRECISION);
        $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $recreationFee);

        // If the person is accomodated on the floor, we do not pay for accomodating him
        $accomodationFee = 0;
        if ($viRegistration['vi_accomodation_type_code'] != 'hala_spacak') {
            $accomodationFee = bcmul(Kohana::config('yosemin.village-accomodationFee'), $viRegistration['night_count'], sql::$DECIMAL_PRECISION);
        }
        $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $accomodationFee);

        if ($writeReason) {
        	$reason = $this->removedFromVillagePaymentReason($viRegistration);
        	if (! $reason) {
            	$reason = '???';
        	}
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $reason);
        }

        foreach ($columnLetters as $column) {
            if ($column == $lastColumn) {
                    break;
            }
            $sheet->getStyle($column . $rowNumber)->applyFromArray($dataCellStyle);
        }
        $sheet->getStyle('A' . $rowNumber)->applyFromArray($nameStyle);
    }

    public function exportEmails() {
        $viSeminarId = $this->input->get('id');
        $mode = $this->input->get('mode');

        $viSeminar = $this->getSeminarById($viSeminarId);

        $personList = $this->selectRegistrationList($viSeminarId, $mode == 'present');

        excel::loadClasses();

        $objPHPExcel = new PHPExcel();

        // Metadata
        $objPHPExcel->getProperties()->setCreator("Sdružení Jóga v denním životě Střílky");
        $objPHPExcel->getProperties()->setLastModifiedBy("Sdružení Jóga v denním životě Střílky");
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();

        $columnLetters = excel::columnLetters();

        $sheet->mergeCells('A1:C1');
        $sheet->setCellValue('A1', $viSeminar['name'] . ' (' . format::date($viSeminar['begin_date']) . ' - ' . format::date($viSeminar['end_date']) . ')');
        $sheet->mergeCells('A2:C2');
        $sheet->setCellValue('A2', text::get('viSeminar.exportEmails') . ' - ' . text::get('viSeminar.participants-' . ($mode == 'present' ? 'presentOnly' : 'all')) . ' (' . format::timestamp(time()) . ')');

        /*---------------------------------
         * Data
         *---------------------------------*/

        $rowNumber = 4;
        $columnNumber = 0;

        foreach ($personList as $person) {
            $columnNumber = 0;

            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $person['last_name']);
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $person['first_name']);
            $sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, $person['email']);

            $rowNumber++;
        }

        $sheet->getColumnDimension('A')->setWidth(22);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(35);

        // Save the file
        $fileName = tempNam(Kohana::config('yosemin.temp_dir'), 'yoseminXlsx');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($fileName);

        download::force($fileName, NULL, string::fileNameReady($viSeminar['name']) . '.xlsx');
    }
    
    public function activate() {
        $id = $this->input->get('id');

        $viSeminar = $this->getSeminarById($id);
        
        $viSeminar['active'] = sql::$TRUE;
        $this->dao->update('vi_seminar', $viSeminar);
        
        $this->msg->info('viSeminar-activate-success');
        $this->redirect('/admin/viSeminar/display?id=' . $id);
    }
    
    private function getSeminarById($id) {
    	if (! valid::isId($id)) {
    		$this->redirectToList('error.invalidId');
    	}
    	
    	$viSeminar = $this->dao->getById('vi_seminar', $id);
    	if (! $viSeminar) {
    		$this->redirectToList('error.recordNotFound');
    	}
    	return $viSeminar;
    }
    
    public function removeAccommodationTypeConfirm() {
    	$viSeminarId = $_GET['id'];
    	$viAccommodationTypeCode = $_GET['vi_accommodation_type_code'];
    	
    	$viSeminar = $this->getSeminarById($viSeminarId);
    	
    	if (! valid::isCode($viAccommodationTypeCode)) {
    		$this->msg->error('error.invalidId');
    		$this->redirect('/admin/viSeminar/display?id=' + $viSeminarId);
    	}
    	$viAccommodationType = $this->dao->getByCode('vi_accomodation_type', $viAccommodationTypeCode);
    	if (! $viAccommodationType) {
    		$this->msg->error('error.recordNotFound');
    		$this->redirect('/admin/viSeminar/display?id=' + $viSeminarId);
    	}
    	
    	$this->viewData['viSeminar'] = $viSeminar;
    	$this->viewData['viAccommodationType'] = $viAccommodationType;
    	$this->viewData['formData'] = array(
    			'vi_seminar_id' => $viSeminar['id'],
    			'vi_accommodation_type_code' => $viAccommodationType['code']
    	);
    	
    	$this->renderMySubview('removeAccommodationConfirm');
    }
    
    public function removeAccommodationType() {
    	$viSeminarId = $_POST['vi_seminar_id'];
    	$viAccommodationTypeCode = $_POST['vi_accommodation_type_code'];
    	
    	$viSeminar = $this->getSeminarById($viSeminarId);
    	
    	$viAccommodationType = $this->dao->getByCode('vi_accomodation_type', $viAccommodationTypeCode);
    	if (! $viAccommodationType) {
    		$this->msg->error('error.recordNotFound');
    		$this->redirect('/admin/viSeminar/display?id=' . $viSeminarId);
    	}
    	
    	$pkValues = array(
    			'vi_seminar_id' => $viSeminarId,
    			'vi_accomodation_type_code' => $viAccommodationTypeCode);
    	$this->dao->delete('vi_seminar_accomodation_type', $pkValues);
    	
    	$pkValues['currency_code'] = constants::$LOCAL_SEMINAR_CURRENCY_CODE;
    	$this->dao->delete('vi_seminar_accomodation_type_price', $pkValues);
    	
    	$this->msg->info('viSeminar-removeAccommodationType-success', $viAccommodationType['name']);
    	
    	$this->redirect('/admin/viSeminar/display?id=' . $viSeminarId);
    }
}
